package com.controller;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import com.model.*;
import java.io.Serializable;
import java.sql.ResultSet;
import java.util.ArrayList;

public class PengendaliPenerimaSuratDokumen implements Serializable{
    public static ArrayList<Surat> DAFTAR_SURAT = new ArrayList<Surat>();
    public static ArrayList<Dokumen> DAFTAR_DOKUMEN = new ArrayList<Dokumen>();

    public static ArrayList<Surat> bacaTabelDaftarSurat(String a,String b) throws SQLException {
        DAFTAR_SURAT= new ArrayList<Surat>();
        Connection conn = OraConnection.open("jdbc:oracle:thin:@localhost:1521:xe","PROJECT-PBO2", "acong");
        Statement statement = conn.createStatement();
        String sql = "select * from surat where tgl_masuk between '"+a+"' and  '"+b+"'";
        ResultSet result = statement.executeQuery(sql);
        while(result.next()){
            Surat temp = new Surat() {};
            temp.setNomorSurat(result.getString("no_surat"));
            temp.setPerihal(result.getString("perihal"));
            temp.setAsalSurat(result.getString("asal_surat"));
            temp.setNamaPenerima(result.getString("penerima_surat"));
            temp.setLetakRuang(result.getString("ruang"));
            temp.setLetakLemari(result.getString("lemari"));
            temp.setLetakLoker(result.getString("loker"));
            temp.setPenerimaSurat(result.getString("petugas_penerima"));
            temp.settanggaMasuk(result.getString("tgl_masuk"));
            temp.setFilescan(result.getString("tipe_file"));
            DAFTAR_SURAT.add(temp);
        }
        conn.close();
         return DAFTAR_SURAT;
    }

    static ArrayList<Surat> bacaTabelDaftar(ArrayList<Surat> test1) {
        throw new UnsupportedOperationException("Not yet implemented");
    }

    public void tambahSurat(Surat surat) {
        DAFTAR_SURAT.add(surat);
    }
    public int jumlahsurat() {
        return DAFTAR_SURAT.size();
    }
    public static void tambahTabelSurat(Surat surat) throws SQLException {
        Connection conn = OraConnection.open("jdbc:oracle:thin:@localhost:1521:xe","MHS115314054", "84376854");
        Statement statement = conn.createStatement();
        String sql = "insert into Surat (no_surat, perihal, asal_surat, penerima_surat, ruang, lemari, loker, petugas_penerima, tgl_masuk, tipe_file) values ('" + surat.getNomorSurat() + "','" + surat.getPerihal()
                    +"','" +surat.getAsalSurat()+"','"+surat.getPenerimaSurat()+"','"+surat.getLetakRuang()+"','" +surat.getLetakLemari()+"','"+surat.getLetakLoker()+"','"+surat.getNamaPenerima()
                    +"','" +surat.getanggalMasuk()+"','"+surat.getFilescan()+"')";
        statement.executeUpdate(sql);
        conn.close();
    }
    public static void tambahTabelDokumen(Dokumen dokumen) throws SQLException {
        Connection conn = OraConnection.open("jdbc:oracle:thin:@localhost:1521:xe","MHS115314054", "84376854");
        Statement statement = conn.createStatement();
        String sql = "insert into Dokumen (no_dokumen, perihal, asal_dokumen, penerima_dokumen, ruang, lemari, loker, petugas_penerima, tgl_masuk, tipe_file) values ('" + dokumen.getnomorDokumen() + "','" + dokumen.getPerihal()
                    +"','" +dokumen.getasalDokumen()+"','"+dokumen.getNamaPenerima()+"','"+dokumen.getLetakRuang()+"','" +dokumen.getLetakLemari()+"','"+dokumen.getLetakLoker()+"','"+dokumen.getpenerimalDokumen()
                    +"','" +dokumen.getTanggalMasuk()+"','"+dokumen.getFilescan()+"')";
        statement.executeUpdate(sql);
        conn.close();
    }
    public static ArrayList<Dokumen> bacaTabelDaftarDokumen(String a,String b) throws SQLException {
        DAFTAR_DOKUMEN= new ArrayList<Dokumen>();
        Connection conn = OraConnection.open("jdbc:oracle:thin:@localhost:1521:xe","PROJECT-PBO2", "acong");
        Statement statement = conn.createStatement();
        String sql = "select * from dokumen where tgl_masuk between '"+a+"' and  '"+b+"'";
        ResultSet result = statement.executeQuery(sql);
        while(result.next()){
            Dokumen temp = new Dokumen() {};
            temp.setnomorDokumen(result.getString("no_dokumen"));
            temp.setPerihal(result.getString("perihal"));
            temp.setasalDokumen(result.getString("asal_dokumen"));
            temp.setNamaPenerima(result.getString("penerima_dokumen"));
            temp.setLetakRuang(result.getString("ruang"));
            temp.setLetakLemari(result.getString("lemari"));
            temp.setLetakLoker(result.getString("loker"));
            temp.setpenerimalDokumen(result.getString("petugas_penerima"));
            temp.setTanggalTerima(result.getString("tgl_masuk"));
            temp.setFilescan(result.getString("tipe_file"));
            DAFTAR_DOKUMEN.add(temp);
        }
        conn.close();
         return DAFTAR_DOKUMEN;
    }
}
